/**
 * 
 */
package metier.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import metier.ElementListe;
import metier.ExceptionMetier;
import metier.Salle;
import metier.Seance;
import metier.Service;

/**
 * @author Stephane AIGUILLON
 *
 */
public class SeanceJDBC extends Seance {
	
	/* Attributs prives */
	private final static String NOMTABLE = "seance"; 	//Nom de la table seance dans la BD
	private final static String IDSEANCE = "id_seance"; //Nom de l'attribut idSeance dans la BD
	private final static String SERVICE = "service"; 	//Nom de l'attribut service dans la BD
	private final static String SALLE = "salle"; 		//Nom de l'attribut salle dans la BD
	private final static String DATE = "date_seance";	//Nom de l'attribut date dans la BD
	private final static String POSITION = "position";	//Nom de l'attribut position dans la BD
	
	//Requete SQL permettant de mettre a jour l'objet dans la base de donnees 
	private final static String updateQuery = "UPDATE " + NOMTABLE + " SET " + SERVICE + " = ?, " + SALLE + " = ?, " + DATE + " = ?, " + POSITION + " = ? where " + IDSEANCE + " = ?";
	//Requete SQL permettant de recuperer la liste de toutes les seances de la base de donnees
	private final static String selectQuery = "SELECT * FROM " + NOMTABLE + " WHERE " + SERVICE + " IS NOT NULL";
	//Requete SQL permettant de recuperer la liste de toutes les seances speciels de la base de donnees
	private final static String selectSpecialQuery = "SELECT * FROM " + NOMTABLE + " WHERE " + SERVICE + " IS NULL";
	//Requete SQL permettant de recuperer la liste de toutes les seances speciels de la base de donnees
	private final static String selectSpecialDateQuery = "SELECT * FROM " + NOMTABLE + " WHERE " + SERVICE + " IS NULL " 
								+ "AND " + DATE + " >= ? AND " + DATE + " <= ? ";
	//Requete SQL permettant de recuperer la liste de toutes les seances speciels de la base de donnees
	private final static String selectQueryMatiere = "SELECT " + NOMTABLE + ".* FROM " + NOMTABLE + " , service, see " 
						+ "WHERE " + NOMTABLE + "." + SERVICE + " = service.id_service " 
						+ "AND service.see = see.id_see AND see.matiere = ?";
	
	//Requete SQL permettant de recuperer la liste de toutes les seances speciels de la base de donnees
	private final static String selectQueryConsultation = "SELECT " + NOMTABLE + "." + IDSEANCE + " FROM " + NOMTABLE + ", service, see, matiere, ue, semestre " 
						+ "WHERE ((" + NOMTABLE + "." + SERVICE + " = service.id_service AND service.see = see.id_see AND see.matiere = matiere.id_matiere "
						+ "AND matiere.ue = ue.id_ue AND ue.semestre = semestre.id_semestre AND semestre.anneeetude = ?) AND " + NOMTABLE + "." + SERVICE + " IS NOT NULL) "
						+ "AND " + NOMTABLE + "." + DATE + " >= ? AND " + NOMTABLE + "." + DATE + " <= ? ";
	private final static String selectQueryConsultationFiltreUE = "AND ue.id_ue = ? ";
	private final static String selectQueryConsultationFiltreEnseignant = "AND service.enseignant = ? ";
	
	//Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
	private final static String loadQuery = "SELECT * FROM " + NOMTABLE + " WHERE " + IDSEANCE + " = ?";
	//Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
	private final static String insertQuery = "INSERT INTO " + NOMTABLE + " VALUES(?, ?, ?, ?, ?)";
	//Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
	private final static String deleteQuery = "DELETE FROM " + NOMTABLE + " WHERE " + IDSEANCE + " = ?";
	private final static String idQuery = "SELECT seq_seance.nextval FROM dual";
	
	/* Attributs prives */
	private int idSeance = 0;		//Identificateur unique de la seance
	
	/**
	 * 
	 */
	public SeanceJDBC(Service service, Salle salle, Date date, int position) {
		super(service, salle, date, position);
		this.idSeance = 0;
	}
	
	/* Accesseurs */
	/**
	 * @return the idSeance
	 */
	protected int getIdSeance() {return idSeance;}
	/**
	 * @param service the service to set
	 */
	protected void setIdSeance(int idSeance) { this.idSeance = idSeance; }

	protected int createId() throws ExceptionMetier {
		try {
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rset = sta.executeQuery(idQuery);
            if (!rset.next())
                throw new ExceptionMetier("creating new id failed due to non generated id");
            int newId = rset.getInt(1);
            rset.close();
            sta.close();
            return newId;
            }
             catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        }
    }
	/* (non-Javadoc)
	 * @see metier.Seance#delete()
	 */
	@Override
	public void delete() throws ExceptionMetier {
		try {
			Connection con = ConnectionJDBC.getConnection();
			PreparedStatement deleteSalle = con.prepareStatement(deleteQuery);
            deleteSalle.setInt(1, getIdSeance());
            deleteSalle.executeQuery();
		}
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Delete failed", ex);} 
	    catch (SQLException ex) {throw new ExceptionMetier("Delete failed", ex);}
	}

	/* (non-Javadoc)
	 * @see metier.Seance#insert()
	 */
	@SuppressWarnings("deprecation")
	@Override
	public void insert() throws ExceptionMetier {
		try {
			Connection con = ConnectionJDBC.getConnection();
			PreparedStatement insertSeance = con.prepareStatement(insertQuery);
			if(getIdSeance() == 0) setIdSeance(createId());
			insertSeance.setInt(1, getIdSeance());
			if (getService() != null) {
				insertSeance.setInt(2, getService().getId());
			} else {
				insertSeance.setObject(2, null);
			}
			insertSeance.setString(3, getSalle().getNom());
			
			java.sql.Date date = new java.sql.Date(getDate().getYear(),getDate().getMonth(), getDate().getDate());
			insertSeance.setDate(4, date);
			insertSeance.setInt(5, getPosition());
			insertSeance.executeQuery();
        } 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Insert failed", ex);} 
        catch (SQLException ex) {throw new ExceptionMetier("Insert failed", ex);}
	}

	/* (non-Javadoc)
	 * @see metier.Seance#update()
	 */
	@Override
	public void update() throws ExceptionMetier {
		try {
			Connection con = ConnectionJDBC.getConnection();
            PreparedStatement updateSeance = con.prepareStatement(updateQuery);
            
            if (getService() != null) {
            	updateSeance.setInt(1, getService().getId());
			} else {
				updateSeance.setObject(1, null);
			}
            updateSeance.setString(2, getSalle().getNom());
            updateSeance.setDate(3, new java.sql.Date(getDate().getTime()));
            updateSeance.setInt(4, getPosition());
            updateSeance.setInt(5, getIdSeance());
            
            //int res = 
            updateSeance.executeUpdate();
        } 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Update failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Update failed", ex);}
	}
	
	@Override
	/* (non-Javadoc)
	 * @see metier.Seance#load(int idSeance)
	 */

	public void load(int idSeance) throws ExceptionMetier {
		try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement loadSeance = con.prepareStatement(loadQuery);
            loadSeance.setInt(1, idSeance);
            ResultSet res = loadSeance.executeQuery();
            if (res.next()) {
	            if (!(res.getInt(IDSEANCE) == idSeance)) {
	                throw new ExceptionMetier("Load failed due to non existing Object");
	            }

	            setIdSeance(res.getInt(IDSEANCE));
	            int serv = res.getInt(SERVICE);
	            if (serv == 0) // service null
	            {
	            	setService(null);
	            } else {
	            	setService(new ServiceJDBC(null, null, null, null));
	            	getService().load(res.getInt(SERVICE));
	            }
	            setSalle(new SalleJDBC("", 0));
	            getSalle().load(res.getString(SALLE));
	            setDate(res.getDate(DATE));
	            setPosition(res.getInt(POSITION));
            } else {
            	throw new ExceptionMetier("Load failed due to non existing Object");
            }
        } 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Load failed", ex);} 
        catch (SQLException ex) {throw new ExceptionMetier("Load failed", ex);}
	}

	/* Methodes statiques */
	/**
	 * Methode qui retourne la liste des seances dans le systeme de stockage
	 * @return ResultSet liste des seances sous forme d'un resultSet
	 * @throws ExceptionMetier 
	 */
	public static ArrayList<ElementListe> select() throws ExceptionMetier {
		ArrayList<ElementListe> listeSeance = new ArrayList<ElementListe>();
		try {
		    ResultSet res = null;
		    Connection con = ConnectionJDBC.getConnection();
		    Statement s = con.createStatement();
		    res = s.executeQuery(selectQuery);
		
		    while (res.next())
			{	
		    	int serv = res.getInt(SERVICE);
		    	Service service = null;
				//recupere le service
		    	if (serv != 0) // service not null
		        {
		    		service = new ServiceJDBC(null, null, null, null);
		           	service.load(res.getInt(SERVICE));
		        }

		    	//recupere la salle
				Salle salle = new SalleJDBC(null, 0);
				salle.load(res.getString(SALLE));
				
				String affichage = res.getDate(DATE).toString() 
				+ " - " + res.getInt(POSITION) + " - " + salle.getNom();
				if (service != null) { affichage += "-" 
					+ service.getEnseignant().getNom()+ " - " + service.getGroupe().getNom();
				}
				
				//Ajoute la seance a la liste
				listeSeance.add(new ElementListe(res.getInt(IDSEANCE), affichage));
			
			} 
		    return listeSeance;
		} 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Select failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Select failed", ex);}
	}
	/**
	 * Methode qui retourne la liste des seances dans le systeme de stockage
	 * @return ResultSet liste des seances sous forme d'un resultSet
	 * @throws ExceptionMetier 
	 */
	public static ArrayList<ElementListe> selectSpecial() throws ExceptionMetier {
		ArrayList<ElementListe> listeSeance = new ArrayList<ElementListe>();
		try {
		    ResultSet res = null;
		    Connection con = ConnectionJDBC.getConnection();
		    Statement s = con.createStatement();
		    res = s.executeQuery(selectSpecialQuery);
		
		    while (res.next())
			{	
				//recupere la salle
				Salle salle = new SalleJDBC(null, 0);
				salle.load(res.getString(SALLE));
				
				//Ajoute la seance a la liste
				listeSeance.add(new ElementListe(res.getInt(IDSEANCE), res.getDate(DATE).toString() 
						+ " - " + res.getInt(POSITION) + " - " + salle.getNom()));
			
			} 
		    return listeSeance;
		} 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Select failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Select failed", ex);}
	}
	/**
	 * Methode qui retourne la liste des seances dans le systeme de stockage
	 * @return ResultSet liste des seances sous forme d'un resultSet
	 * @throws ExceptionMetier 
	 */
	public static ArrayList<Seance> selectSpecial(Date deb, Date fin) throws ExceptionMetier {
		ArrayList<Seance> listeSeance = new ArrayList<Seance>();
		try {
		    ResultSet res = null;
		    Connection con = ConnectionJDBC.getConnection();
		    PreparedStatement select = con.prepareStatement(selectSpecialDateQuery);
		    select.setDate(1, new java.sql.Date(deb.getTime()));
		    select.setDate(2, new java.sql.Date(fin.getTime()));
		    
		    res = select.executeQuery();
		
		    while (res.next())
			{
		    	SeanceJDBC seance = new SeanceJDBC(null, null, null, 0);;
				seance.load(res.getInt(IDSEANCE));
				listeSeance.add(seance);
			} 
		    return listeSeance;
		} 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Select failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Select failed", ex);}
	}
	   /**
	 * Methode qui retourne la liste des matieres dans le systeme de stockage
	 * @return ArrayList<ElementListe> liste des matieres sous forme d'un ArrayList de ElementListe
	 * @throws ExceptionMetier 
	 */
	public static ArrayList<ElementListe> selectMatiere(int matiere) throws ExceptionMetier {
		ArrayList<ElementListe> listeSeance = new ArrayList<ElementListe>();
		try {
		    ResultSet res = null;
		    Connection con = ConnectionJDBC.getConnection();
		    PreparedStatement select = con.prepareStatement(selectQueryMatiere);
            select.setInt(1, matiere);
           
		    res = select.executeQuery();
		
		    while (res.next())
			{
		    	//recupere la salle
				Salle salle = new SalleJDBC(null, 0);
				salle.load(res.getString(SALLE));
				
		    	//Ajoute la seance a la liste
				listeSeance.add(new ElementListe(res.getInt(IDSEANCE), res.getDate(DATE).toString() 
						+ " - " + res.getInt(POSITION) + " - " + salle.getNom()));
			} 
		    return listeSeance;
		} 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Select failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Select failed", ex);}
	}
	  /**
	 * Methode qui retourne la liste des matieres dans le systeme de stockage
	 * @return ArrayList<ElementListe> liste des matieres sous forme d'un ArrayList de ElementListe
	 * @throws ExceptionMetier 
	 */
	public static ArrayList<Seance> selectConsultation(int annee, int ue, int enseignant, Date deb, Date fin) throws ExceptionMetier {
		ArrayList<Seance> listeSeance = new ArrayList<Seance>();
		try {
		    ResultSet res = null;
		    PreparedStatement select = null; 
		    Connection con = ConnectionJDBC.getConnection();
		    String requete = selectQueryConsultation;
		    if (ue != -1) { //Filtre UE Actif
		    	requete += selectQueryConsultationFiltreUE;
		    	if (enseignant != -1) { //Filtre Enseignant Actif
		    		requete += selectQueryConsultationFiltreEnseignant; 
		    		
		    		select = con.prepareStatement(requete);
		    		select.setInt(1, annee);
		            select.setDate(2, new java.sql.Date(deb.getTime()));
		            select.setDate(3, new java.sql.Date(fin.getTime()));
		    		select.setInt(4, ue);
		    		select.setInt(5, enseignant);

		    	} else { //Filtre Enseignant INACTIF
		    		select = con.prepareStatement(requete);
		    		select.setInt(1, annee);
		            select.setDate(2, new java.sql.Date(deb.getTime()));
		            select.setDate(3, new java.sql.Date(fin.getTime()));
		    		select.setInt(4, ue);
		    	}
		    } else { //Filtre UE INACTIF
		    	if (enseignant != -1) { //Filtre Enseignant Actif
		    		requete += selectQueryConsultationFiltreEnseignant; 
		    		
		    		select = con.prepareStatement(requete);
		    		select.setInt(1, annee);
		            select.setDate(2, new java.sql.Date(deb.getTime()));
		            select.setDate(3, new java.sql.Date(fin.getTime()));
		    		select.setInt(4, enseignant);

		    	} else { //Filtre Enseignant INACTIF
		    		
		    		select = con.prepareStatement(requete);
		    		select.setInt(1, annee);
		            select.setDate(2, new java.sql.Date(deb.getTime()));
		            select.setDate(3, new java.sql.Date(fin.getTime()));
		    	}
		    }

		    res = select.executeQuery();
		
		    while (res.next())
			{
		    	SeanceJDBC seance = new SeanceJDBC(null, null, null, 0);;
				seance.load(res.getInt(IDSEANCE));
				listeSeance.add(seance);
			} 
		    return listeSeance;
		} 
		catch (ClassNotFoundException ex) { throw new ExceptionMetier("Select failed", ex); } 
		catch (SQLException ex) { throw new ExceptionMetier("Select failed", ex); }
	}

	@Override
	public void echangerSeance(Seance seance) throws ExceptionMetier {
		Service servTemp = getService();
		setService(seance.getService());
		seance.setService(servTemp);
		this.update();
		seance.update();
	}
}
